﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_Nhomthucpham_ThucanDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();

        public DataSet FindSameName(string VarTen)
        {
            string NamePrc = "TblDm_Nhomthucpham_Thucan_FindNameSamePrc";
            string NameTable = "TblDm_Nhomthucpham_Thucan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            SqlCommand Cmm = new SqlCommand();
            Cmm.Parameters.Add(new SqlParameter("@VarTen", VarTen));

            Cmm.Connection = ClsConnection.SQLConnect();
            Cmm.CommandText = NamePrc;
            Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {

            }
            finally
            {
                Cmm.Connection.Close();
            }
            SqlDataAdapter Dap;
            Dap = new SqlDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[NameTable]);

            return Dset;
        }

        public BindingSource SelectAll()
        {
            string NamePrc = QueryString.TBLDM_NHOMTHUCPHAM_THUCAN_SELECTALL_PRC;
            string NameTable = "TblDm_Nhomthucpham_thucan";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;


            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);

            return Binding;

        }

        public void Insert(string Ten)
        {
            string NamePrc = "TblDm_Nhomthucpham_thucan_InsertPrc";
            SqlCommand Cmm = new SqlCommand();

            SqlParameter Ten_Paramet = new SqlParameter();

            Ten_Paramet.ParameterName = "@Tennhom";
            Ten_Paramet.SqlDbType = System.Data.SqlDbType.NVarChar;
            Ten_Paramet.Value = Ten;

            Cmm.Parameters.Add(Ten_Paramet);
            ClsConnection.SqlDAdpter(NamePrc, Cmm);
        }

        public void Edit(int Id, string Ten)
        {
            string NamePrc = "TblDm_Nhomthucpham_thucan_EditPrc";
            SqlCommand Cmm = new SqlCommand();
            SqlParameter Id_Paramet = new SqlParameter();
            SqlParameter Ten_Paramet = new SqlParameter();

            Id_Paramet.ParameterName = "@Id";
            Id_Paramet.SqlDbType = System.Data.SqlDbType.Int;
            Id_Paramet.Value = Id;

            Ten_Paramet.ParameterName = "@Tennhom";
            Ten_Paramet.SqlDbType = System.Data.SqlDbType.NVarChar;
            Ten_Paramet.Value = Ten;


            Cmm.Parameters.Add(Id_Paramet);
            Cmm.Parameters.Add(Ten_Paramet);

            ClsConnection.SqlDAdpter(NamePrc, Cmm);

        }

        public void Del(int Id)
        {
            string NamePrc = "TblDm_Nhomthucpham_thucan_Del";
            SqlCommand Cmm = new SqlCommand();
            SqlParameter Id_Paramet = new SqlParameter();
            SqlParameter Ten_Paramet = new SqlParameter();

            Id_Paramet.ParameterName = "@Id";
            Id_Paramet.SqlDbType = System.Data.SqlDbType.Int;
            Id_Paramet.Value = Id;



            Cmm.Parameters.Add(Id_Paramet);

            ClsConnection.SqlDAdpter(NamePrc, Cmm);

        }

        public static DataTable GetTable()
        {
            DataTable table = new DataTable();

            DataColumn colum = new DataColumn();
            colum.ColumnName = colum.Caption = "STT";
            colum.DataType = System.Type.GetType("System.Int32");
            colum.AllowDBNull = false;
            colum.AutoIncrement = true;
            colum.AutoIncrementSeed = colum.AutoIncrementStep = 1;
            table.Columns.Add(colum);

            string namePro = QueryString.TBLDM_NHOMTHUCPHAM_THUCAN_SELECTALL_PRC;
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand comand = new OleDbCommand(namePro, cn);
            try
            {
                comand.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(comand);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }
    }
}
